﻿using HCQ2_IDAL;
using HCQ2_Model.DocModel.APIResultModel;
using HCQ2_Model.VR.Params;
using System;
using System.Collections.Generic;
using System.Data;
using System.Globalization;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace HCQ2_DAL_MSSQL
{
    public partial class T_VRExperienceDetailDAL: IT_VRExperienceDetailDAL
    {
        /// <summary>
        ///  获取体验明细
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public List<ExperRecordDetailModel> GetExperDetailList(ExperRecordModel param,out int total)
        {
            StringBuilder query = new StringBuilder();
            sb?.Clear();
            sb.AppendFormat(@"SELECT TOP {0} * FROM 
            (SELECT record.user_name,record.file_name,record.folder_name,detail.*, ", param.rows);
            switch (param.orderType)
            {
                case 2: sb.AppendFormat(" ROW_NUMBER() OVER(ORDER BY detail.score DESC)AS rowNumber "); break;//得分降序
                case 3: sb.AppendFormat(" ROW_NUMBER() OVER(ORDER BY detail.score ASC)AS rowNumber "); break;//得分升序
                default: sb.AppendFormat(" ROW_NUMBER() OVER(ORDER BY detail.in_date DESC)AS rowNumber "); break;//体验时间降序
            }
            sb.AppendFormat(" FROM  (SELECT er_id, user_name, file_name, folder_name FROM dbo.T_VRExperienceRecord WHERE 1 = 1 ");
            //体验用户
            if (!string.IsNullOrEmpty(param.userName))
                sb.AppendFormat(" AND user_name LIKE '{0}%' ", param.userName);
            //体验作品
            if(!string.IsNullOrEmpty(param.fileName))
                sb.AppendFormat(" AND file_name like '{0}%' ", param.fileName);
            //体验关卡
            if (!string.IsNullOrEmpty(param.folderName))
                sb.AppendFormat(" AND folder_name like '{0}%' ", param.folderName);
            sb.AppendFormat(" )record INNER JOIN (SELECT er_id, ed_id, word, CAST(ed_status AS INT)AS ed_status, score, CONVERT(varchar(100), in_date, 120) AS in_date FROM dbo.T_VRExperienceDetail WHERE 1 = 1 ");
            //单词
            if (!string.IsNullOrEmpty(param.word))
                query.AppendFormat(" AND word LIKE '{0}%' ", param.word);
            //状态
            switch(param.statusType)
            {
                case "yes": query.AppendFormat(" AND ed_status=1 "); break;
                case "error": query.AppendFormat(" AND ed_status=0 "); break;
            }
            //时间
            if (!string.IsNullOrEmpty(param.inDate) && !string.IsNullOrEmpty(param.outDate))
                query.AppendFormat(" AND in_date>= '{0}' AND in_date<='{1}' ", DateTime.ParseExact(param.inDate, "yyyy-MM-dd", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString(), DateTime.ParseExact(param.outDate, "yyyy-MM-dd", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString());
            else if (!string.IsNullOrEmpty(param.inDate) && string.IsNullOrEmpty(param.outDate))
                query.AppendFormat(" AND in_date>= '{0}' ", DateTime.ParseExact(param.inDate, "yyyy-MM-dd", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString());
            else if (string.IsNullOrEmpty(param.inDate) && !string.IsNullOrEmpty(param.outDate))
                query.AppendFormat(" AND in_date<= '{0}' ", DateTime.ParseExact(param.outDate, "yyyy-MM-dd", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString());
            sb.AppendFormat(query.ToString());
            sb.AppendFormat(")detail ON detail.er_id = record.er_id)A WHERE A.rowNumber>{0};", (param.page - 1) * param.rows);
            //计算数量
            StringBuilder to = new StringBuilder();
            to.AppendFormat("SELECT COUNT(0) FROM (SELECT er_id FROM dbo.T_VRExperienceRecord WHERE 1 = 1 ");
            //体验用户
            if (!string.IsNullOrEmpty(param.userName))
                to.AppendFormat("AND (user_name LIKE '{0}%' OR file_name like '{0}%' OR folder_name like '{0}%') ", param.userName);
            to.AppendFormat(")record INNER JOIN (SELECT er_id FROM dbo.T_VRExperienceDetail WHERE 1 = 1 ");
            to.AppendFormat(query.ToString());
            to.AppendFormat(")detial ON detial.er_id = record.er_id;");
            total= HCQ2_Common.Helper.ToInt(HCQ2_Common.SQL.SqlHelper.ExecuteScalar(to.ToString()));
            DataTable dt = HCQ2_Common.SQL.SqlHelper.ExecuteDataTable(sb.ToString());
            return HCQ2_Common.Data.DataTableHelper.DataTableToIList<ExperRecordDetailModel>(dt);
        }
        /// <summary>
        ///  获取作品题目数
        /// </summary>
        /// <param name="file_id"></param>
        /// <returns></returns>
        public int GetWorkNum(int file_id)
        {
            sb?.Clear();
            sb.Append($"SELECT COUNT(0) AS num FROM (SELECT DISTINCT word FROM dbo.T_VRExperienceDetail WHERE file_id={file_id})a ;");
            return HCQ2_Common.Helper.ToInt(HCQ2_Common.SQL.SqlHelper.ExecuteScalar(sb.ToString()));
        }
    }
}
